package com.xingling.util.excel;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 通用导出excel，用户将list<Map>数据导出为excel
 *
 * @author yangwensheng
 */
public class ExcelWriteMapUtil extends ExcelWriteAbstractUtil {

    private static Logger log = LoggerFactory.getLogger(ExcelWriteMapUtil.class);

    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 当前行号
     */
    private int rowNum;

    List<String> expFieldList = Lists.newArrayList();

    public ExcelWriteMapUtil(String title, List<String> headerList, List<String> expFieldList, String version) {
        this.expFieldList = expFieldList;
        initialize(title, headerList, version, "");
    }

    /**
     * 给可以用作上传模板的EXCEL加标记
     */
    public ExcelWriteMapUtil(String title, List<String> headerList, List<String> expFieldList, String version, String templateType) {
        this.expFieldList = expFieldList;
        initialize(title, headerList, version, templateType);
    }

    /**
     * 输出到客户端
     *
     * @param fileName 输出文件名
     */
    public ExcelWriteMapUtil write(HttpServletRequest request, HttpServletResponse response, String fileName) throws IOException {
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + encodeChineseDownloadFileName(request, fileName));
        write(response.getOutputStream());
        return this;
    }

    /**
     * 输出数据流
     *
     * @param os 输出数据流
     */
    public ExcelWriteMapUtil write(OutputStream os) throws IOException {
        try {
            wb.write(os);
        } finally {
            if (os != null) {
                os.close();
            }
        }
        return this;
    }

    /**
     * 添加一个单元格
     *
     * @param row    添加的行
     * @param column 添加列号
     * @param val    添加值
     * @param align  对齐方式（1：靠左；2：居中；3：靠右）
     * @return 单元格对象
     */
    public Cell addCell(Row row, int column, Object val, int align) {
        Cell cell = row.createCell(column);
        CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
        try {
            if (val == null) {
                cell.setCellValue("");
            } else if (val instanceof String) {
                cell.setCellValue((String) val);
            } else if (val instanceof Integer) {
                cell.setCellValue((Integer) val);
            } else if (val instanceof Long) {
                cell.setCellValue((Long) val);
            } else if (val instanceof Double) {
                cell.setCellValue((Double) val);
            } else if (val instanceof Float) {
                cell.setCellValue((Float) val);
            } else if (val instanceof Date) {
                cell.setCellValue((Date) val);
                CellStyle dateStyle = styles.get("date");
                cell.setCellStyle(dateStyle);

            } else {
                cell.setCellValue(val.toString());
            }
        } catch (Exception ex) {
            log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
            if (val != null) {
                cell.setCellValue(val.toString());
            }
        }
        if (!(val instanceof Date)) {
            cell.setCellStyle(style);
        }

        return cell;
    }

    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = Maps.newHashMap();

        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        styles.put("data3", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        DataFormat dateFormat = wb.createDataFormat();
        style.setDataFormat(dateFormat.getFormat("yyyy-MM-dd"));
        styles.put("date", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

    /**
     * 初始化函数
     *
     * @param title      表格标题，传“空值”，表示无标题
     * @param headerList 表头列表
     */
    private void initialize(String title, List<String> headerList, String version, String templateType) {
        if (version.equals("2003")) {
            this.wb = new HSSFWorkbook();
        } else {
            this.wb = new SXSSFWorkbook(500);
        }
        this.sheet = wb.createSheet("Export");
        this.styles = createStyles(wb);

        if (StringUtils.isNotBlank(templateType)) {
            Sheet checkSheet = wb.createSheet("check");
            Row checkRow = checkSheet.createRow(0);
            Cell checkCell = checkRow.createCell(0);
            checkCell.setCellValue(templateType);
        }

        // Create title
        if (StringUtils.isNotBlank(title)) {
            Row titleRow = sheet.createRow(rowNum++);
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(styles.get("title"));
            titleCell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
                    titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1));
        }
        // Create header
        if (headerList == null) {
            throw new RuntimeException("headerList not null!");
        }
        Row headerRow = sheet.createRow(rowNum++);
        headerRow.setHeightInPoints(16);
        for (int i = 0; i < headerList.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(styles.get("header"));
            String[] ss = StringUtils.split(headerList.get(i), "**", 2);
            if (ss.length == 2) {
                cell.setCellValue(ss[0]);
                Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
                        new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
                comment.setString(new XSSFRichTextString(ss[1]));
                cell.setCellComment(comment);
            } else {
                cell.setCellValue(headerList.get(i));
            }
        }
        for (int i = 0; i < headerList.size(); i++) {
            int colWidth = sheet.getColumnWidth(i) * 2;
            sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
        }
        log.debug("Initialize success.");
    }

    /**
     * 添加一行
     *
     * @return 行对象
     */
    public Row addRow() {
        return sheet.createRow(rowNum++);
    }

    public ExcelWriteMapUtil setDataList(List<Map> list) {
        //noinspection unchecked
        for (Map<String, Object> data : list) {
            int column = 0;
            Row row = this.addRow();
            for (String os : expFieldList) {
                String result;
                try {
                    if (!os.equals("operation_type")) {
                        Object val = data.get(os);
                        if (val == null) {
                            val = "";
                        }
                        if (val instanceof Date) {
                            result = com.xingling.util.DateUtil.dateToStr((Date) val, "yyyy-MM-dd");
                        } else {
                            result = String.valueOf(val);
                        }
                        this.addCell(row, column++, result, 3);
                    } else {
                        this.addCell(row, column, "已付款", 3);
                        //设置下拉列表的内容
                        String[] textList = {"已付款", "未成交"};
                        int rowNum = row.getRowNum();

                        //数据有效性对象
                        if (sheet instanceof HSSFSheet) {
                            setDataValidationHSSF(sheet, textList, rowNum, rowNum, column, column);
                        } else {
                            setValidateXSSF(sheet, rowNum, rowNum, column, column, textList);
                        }

                        column++;
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            //log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
        }
        return this;
    }

    private void setDataValidationHSSF(Sheet sheet, String[] textList, int rowNum, Integer rowNumEnd, int column, int columnEnd) {
        //加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textList);
        //设置数据有效性加载在哪个单元格上。
        //四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(rowNum, rowNumEnd, column, columnEnd);
        HSSFDataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(dataValidationList);
    }

    public static DataValidation setValidateXSSF(Sheet sheet, int rowNum,
                                                 int rowNumEnd, int column, int columnEnd, String[] dataList) {
        XSSFDataValidationHelper helper = (XSSFDataValidationHelper) sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(dataList);
        CellRangeAddressList regions = new CellRangeAddressList(rowNum,
                rowNumEnd, column, columnEnd);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        sheet.addValidationData(dataValidation);
        return dataValidation;
    }

    public ExcelWriteMapUtil setMapDataList(List<Map<String, Object>> list) {
        for (Map<String, Object> data : list) {
            int column = 0;
            Row row = this.addRow();
            for (String os : expFieldList) {
                String result = "";
                try {
                    Object val = data.get(os);
                    if (val == null) {
                        val = "";
                    }
                    if (val instanceof Date) {
                        result = com.xingling.util.DateUtil.dateToStr((Date) val, "yyyy-MM-dd");
                    } else {
                        result = String.valueOf(val);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
                this.addCell(row, column++, result, 3);

            }
            log.debug("Write success: [" + row.getRowNum() + "] " + "");
        }
        return this;
    }
}
